﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
namespace SaaS
{
    public partial class SetForeignKey : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["LoginID"] == null)
            {
                Response.Redirect("UserLogin.aspx");
                return;
            }
            if (Session.Count != 0 & ddlForeignTable.Items.Count == 0)
            {
                ddlForeignKey.Items.Clear();
                ddlForeignTable.Items.Clear();
                ddlPrimaryKey.Items.Clear();
                ddlPrimaryTable.Items.Clear();

                string loginID = Session["LoginID"].ToString();
                String strSQL = "SELECT DISTINCT Table_ID FROM META WHERE Login_ID = " + loginID + ";";
                SaaS.MiniSaaSDB db = new SaaS.MiniSaaSDB();
                db.open();
                System.Data.SqlClient.SqlDataReader Dr;
                Dr = db.executeQuery(strSQL);
                System.Collections.Generic.List<int> tableIDs = new System.Collections.Generic.List<int>();
                while (Dr.Read())
                    tableIDs.Add((int)Dr["Table_ID"]);
                Dr.Close();
                for (int i = 0; i < tableIDs.Count; i++)
                {
                    strSQL = "SELECT Table_Name FROM META WHERE Table_ID = " + tableIDs[i].ToString() +
                        " AND Table_Version = (SELECT MAX(Table_Version) FROM META WHERE Table_ID = " +
                        tableIDs[i].ToString() + ")";
                    Dr = db.executeQuery(strSQL);
                    Dr.Read();
                    ddlForeignTable.Items.Add(Dr["Table_Name"].ToString());
                    ddlPrimaryTable.Items.Add(Dr["Table_Name"].ToString());
                    Session["TableID" + i.ToString()] = tableIDs[i].ToString();
                    Dr.Close();
                }
                db.close();
                ddlForeignTable_SelectedIndexChanged(null, null);
                ddlPrimaryTable_SelectedIndexChanged(null, null);
            }
        }

        protected void ddlForeignTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            lblErr.Text = "";
            if (ddlForeignTable.Items.Count <= 0) return;
            //fill ddlForeignKey with all attributes in selected table
            ddlForeignKey.Items.Clear();
            string query = "SELECT * FROM META WHERE Table_ID = " + 
                Session["TableID" + ddlForeignTable.SelectedIndex.ToString()].ToString() + 
                " AND Table_Version = (SELECT MAX(Table_Version) FROM META WHERE Table_ID = " +
                Session["TableID" + ddlForeignTable.SelectedIndex.ToString()].ToString() +");";
            SaaS.MiniSaaSDB db = new SaaS.MiniSaaSDB();
            db.open();
            System.Data.SqlClient.SqlDataReader Dr = db.executeQuery(query);
            if (Dr == null || !Dr.HasRows)
            {
                lblErr.Text = "ERROR - Could not retrieve data";
                return;
            }
            Dr.Read();
            XmlDocument xd = new XmlDocument();
            xd.LoadXml(Dr["Table_Columns"].ToString());
            XmlNodeList xnl = xd.SelectNodes("//col");
            Session["xml"] = Dr["Table_Columns"].ToString();
            for (int i = 0; i < xnl.Count; i++)
            {
                ddlForeignKey.Items.Add(xnl[i].FirstChild.InnerText);
                Session["FKCol" + i.ToString()] = xnl[i].ChildNodes[4].InnerText;
                Session["FKType" + i.ToString()] = xnl[i].ChildNodes[1].InnerText;
            }
            db.close();
        }

        protected void ddlPrimaryTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            lblErr.Text = "";
            if (ddlPrimaryTable.Items.Count <= 0) return;
            //fill ddlPrimaryKey with all primary keys in selected table
            ddlPrimaryKey.Items.Clear();
            string query = "SELECT * FROM META WHERE Table_ID = " +
                Session["TableID" + ddlForeignTable.SelectedIndex.ToString()].ToString() +
                " AND Table_Version = (SELECT MAX(Table_Version) FROM META WHERE Table_ID = " +
                Session["TableID" + ddlForeignTable.SelectedIndex.ToString()].ToString() + ");";
            SaaS.MiniSaaSDB db = new SaaS.MiniSaaSDB();
            db.open();
            System.Data.SqlClient.SqlDataReader Dr = db.executeQuery(query);
            if (Dr == null || !Dr.HasRows)
            {
                lblErr.Text = "ERROR - Could not retrieve data";
                return;
            }
            Dr.Read();
            XmlDocument xd = new XmlDocument();
            xd.LoadXml(Dr["Table_Columns"].ToString());
            XmlNodeList xnl = xd.SelectNodes("//col");
            for (int i = 0; i < xnl.Count; i++)
            {
                if (xnl[i].ChildNodes[5].InnerText.Equals("True", StringComparison.CurrentCultureIgnoreCase))
                {
                    ddlPrimaryKey.Items.Add(xnl[i].FirstChild.InnerText);
                    Session["PKCol" + i.ToString()] = xnl[i].ChildNodes[4].InnerText;
                    Session["PKType" + i.ToString()] = xnl[i].ChildNodes[1].InnerText;
                }
            }
            db.close();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            lblErr.Text = "";
            //check that the types of primary and foreign match
            if (!Session["FKType" + ddlForeignKey.SelectedIndex.ToString()].ToString().Equals(
                Session["FKType" + ddlPrimaryKey.SelectedIndex.ToString()].ToString()))
            {
                lblErr.Text = "Foreign key and primary key types do not match";
                return;
            }
            XmlDocument xd = new XmlDocument();
            xd.LoadXml(Session["xml"].ToString());
            XmlNodeList xnl = xd.SelectNodes("//col");
            for (int i = 0; i < xnl.Count; i++)
            {
                if (xnl[i].ChildNodes[4].InnerText.Equals(Session["FKCol" + ddlForeignKey.SelectedIndex.ToString()].ToString()))
                {
                    //Set foreign table ID
                    xnl[i].ChildNodes[6].ChildNodes[0].InnerText = Session["TableID" + ddlPrimaryTable.SelectedIndex.ToString()].ToString();
                    //set the foreign column ID
                    xnl[i].ChildNodes[6].ChildNodes[1].InnerText = Session["PKCol" + ddlPrimaryKey.SelectedIndex.ToString()].ToString();
                    break;
                }
            }
            
            string newColumns = xd.OuterXml;
            string query;
            string foreignTableID = Session["TableID" + ddlForeignTable.SelectedIndex.ToString()].ToString();
            SaaS.MiniSaaSDB db = new SaaS.MiniSaaSDB();
            db.open();
            query = "SELECT * FROM DATA WHERE Table_ID = " + foreignTableID +
                " AND Table_Version = (SELECT MAX(Table_Version) FROM META WHERE Table_ID = " +
                foreignTableID + ");";
            System.Data.SqlClient.SqlDataReader Dr = db.executeQuery(query);
            if (Dr != null && Dr.HasRows)
            {
                //create new entry
                query = "INSERT INTO META (Table_ID, Table_Version, Login_ID, Table_Name, Table_Columns_Count, Table_Columns, Table_Description)" +
                    " SELECT Table_ID, (SELECT MAX(Table_Version)+1 FROM META WHERE Table_ID = " + foreignTableID + "), " +
                    "Login_ID, Table_Name, Table_Columns_Count, '" + newColumns + "', Table_Description FROM META WHERE " +
                    "Table_ID = " + foreignTableID + " AND Table_Version = (SELECT MAX(Table_Version) FROM META WHERE " +
                    "Table_ID = " + foreignTableID + ");";
            }
            else
            {
                //update
                query = "UPDATE META " +
                    "SET Table_Columns = '" + newColumns + "' " +
                    "WHERE Table_ID = " + foreignTableID  + " AND " +
                    "Table_Version = (SELECT MAX(Table_Version) FROM META WHERE Table_ID = " + foreignTableID + ");";
            }
            int rows = db.executeNonQuery(query);
            if (rows <= 0)
            {
                lblErr.Text = "Table not updated";
            }
            db.close();
        }
    }
}
